package com.etc.team2.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.etc.team2.bean.StaToSta;
import com.etc.team2.jdbc.JDBCHelper;

public class STSDAO {
	
	private JDBCHelper helper=null;
	
	public STSDAO()
	{
		helper=new JDBCHelper();
	}
	
	public List<List> Exchange(String sname,String ename)
	{
		int flog=0;
		List<List> list=new ArrayList<List>();
		
		
		
		String sql1="select a.lineName from bus_line_info a,bus_station_info b,bus_station_info c,view_stos d where" +
				" d.startfrom=b.stationId and d.endto=c.stationId and d.line=a.lineId and b.stationName='"+sname+"' and c.stationName='"+ename+"'";
		String sql2="select c.lineName,e.stationName,g.lineName from view_stos a,view_stos b,bus_line_info c,bus_station_info d,bus_station_info e," +
				"bus_station_info f,bus_line_info g where d.stationName='"+sname+"' and f.stationName='"+ename+"' and a.startfrom=d.stationId and " +
						"b.endto=f.stationId and a.endto=b.startfrom and a.line=c.lineId and b.line=g.lineId and e.stationId=a.endto";
		String sql3="select e.lineName,b.stationName,f.lineName,c.stationName,g.lineName from bus_station_info a, bus_station_info b," +
				"bus_station_info c,bus_station_info d,bus_line_info e,bus_line_info f,bus_line_info g,view_stos x,view_stos y,view_stos z " +
				"where a.stationName='"+sname+"' and d.stationName='"+ename+"' and x.startfrom=a.stationId and z.endto=d.stationId " +
						"and x.endto=y.startfrom and y.endto=z.startfrom and e.lineId=x.line and f.lineId=y.line and g.lineId=z.line and " +
						" b.stationId=x.endto and c.stationId=y.endto";
		ResultSet set1=helper.executeQueryByPrepareStatement(sql1, null);
		ResultSet set2=helper.executeQueryByPrepareStatement(sql2, null);
		ResultSet set3=helper.executeQueryByPrepareStatement(sql3, null);
		try {
			while(set1.next())
			{
				List<String> list1=new ArrayList<String>();
				list1.add(sname);
				list1.add(set1.getString(1));
				list1.add(ename);
				list.add(list1);
				flog=1;
			}
			if(flog==0)
			{
			while(set2.next())
			{
				List<String> list2=new ArrayList<String>();
				list2.add(sname);
				list2.add(set2.getString(1));
				list2.add(set2.getString(2));
				list2.add(set2.getString(3));
				list2.add(ename);
				list.add(list2);
				flog=1;
			}
			if(flog==0)
			{
			while(set3.next())
			{
				List<String> list3=new ArrayList<String>();
				list3.add(sname);
				list3.add(set3.getString(1));
				list3.add(set3.getString(2));
				list3.add(set3.getString(3));
				list3.add(set3.getString(4));
				list3.add(set3.getString(5));
				list3.add(ename);
				list.add(list3);
			}
			}
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return list;
	}

}
